package com.example.jdbconectiontest.controller;

import com.example.jdbconectiontest.beans.User;
import com.example.jdbconectiontest.config.MyMysqlPool;
import com.mysql.cj.jdbc.MysqlConnectionPoolDataSource;
import com.mysql.cj.jdbc.MysqlDataSource;
import lombok.val;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.sql.PooledConnection;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.WeakHashMap;

@RestController
public class JdbcController {
    @Autowired
    MysqlConnectionPoolDataSource mysqlConnectionPoolDataSource;
    @Autowired
    MysqlDataSource mysqlDataSource;
    @Autowired
    MyMysqlPool myMysqlPool;

    @GetMapping("/pooledquery")
    public String pooledquery(@RequestParam String name) throws SQLException {
        long start = System.currentTimeMillis();
        PooledConnection pooledConnection = mysqlConnectionPoolDataSource.getPooledConnection();
        Connection connection = pooledConnection.getConnection();
        Statement statement = connection.createStatement();
        String sql = "select * from user where name like " + "'" + name + "%" + "'";

        ResultSet resultSet = statement.executeQuery(sql);
        List<User> list = new ArrayList<>();
        while (resultSet.next()) {
            list.add(new User(resultSet.getString("id"), resultSet.getString("name"),
                    resultSet.getString("age"), resultSet.getString("sex"),
                    resultSet.getString("adress")));
        }
        System.out.println(sql);
        System.out.println(list.size());
        connection.close();
        long end = System.currentTimeMillis();
        System.out.println("数据库查询用时：" + (float) ((end - start)) / 1000 + "秒");
        return list.toString();
    }

    @GetMapping("/myquery")
    public String myquery(@RequestParam String name) throws SQLException {
        long start = System.currentTimeMillis();
        Connection connection = myMysqlPool.getConnection();
        Statement statement = connection.createStatement();
        String sql = "select * from user where name like " + "'" + name + "%" + "'";
        ResultSet resultSet = statement.executeQuery(sql);
        List<User> list = new ArrayList<>();
        while (resultSet.next()) {
            list.add(new User(resultSet.getString("id"), resultSet.getString("name"),
                    resultSet.getString("age"), resultSet.getString("sex"),
                    resultSet.getString("adress")));
        }
        System.out.println(sql);
        System.out.println(list.size());
        myMysqlPool.closeConnection(connection);
        long end = System.currentTimeMillis();
        System.out.println("数据库查询用时：" + (float) ((end - start)) / 1000 + "秒");
        return list.toString();
    }

    @GetMapping("/query")
    public String query(@RequestParam String name) throws SQLException {
        long start = System.currentTimeMillis();
        Connection connection = myMysqlPool.mysqlDataSource().getConnection();
        Statement statement = connection.createStatement();
        String sql = "select * from user where name like " + "'" + name + "%" + "'";
        ResultSet resultSet = statement.executeQuery(sql);
        List<User> list = new ArrayList<>();
        while (resultSet.next()) {
            list.add(new User(resultSet.getString("id"), resultSet.getString("name"),
                    resultSet.getString("age"), resultSet.getString("sex"),
                    resultSet.getString("adress")));
        }
        System.out.println(sql);
        System.out.println(list.size());
        connection.close();
        long end = System.currentTimeMillis();
        System.out.println("数据库查询用时：" + (float) ((end - start)) / 1000 + "秒");
        return list.toString();
    }


}
